package com.example.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.project.entity.IssueCategoryStats;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 需求类别统计数据Mapper接口
 */
@Mapper
public interface IssueCategoryStatsMapper {

    /**
     * 插入需求类别统计数据
     */
    @Insert("INSERT INTO t_issue_category_stats " +
            "(category_id, tracker_id, priority_id, project_id, sample_count, avg_processing_time_hours, " +
            "min_processing_time_hours, max_processing_time_hours, success_rate, retry_rate, last_updated) " +
            "VALUES (#{categoryId}, #{trackerId}, #{priorityId}, #{projectId}, #{sampleCount}, " +
            "#{avgProcessingTimeHours}, #{minProcessingTimeHours}, #{maxProcessingTimeHours}, " +
            "#{successRate}, #{retryRate}, NOW())")
    int insertIssueCategoryStats(IssueCategoryStats stats);

    /**
     * 根据项目ID获取类别统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE project_id = #{projectId} ORDER BY last_updated DESC")
    List<IssueCategoryStats> getStatsByProjectId(@Param("projectId") Integer projectId);

    /**
     * 根据需求类别获取统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE tracker = #{tracker} ORDER BY last_updated DESC")
    List<IssueCategoryStats> getStatsByTracker(@Param("tracker") String tracker);

    /**
     * 根据项目ID和需求类别获取统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE project_id = #{projectId} AND tracker = #{tracker} ORDER BY last_updated DESC")
    List<IssueCategoryStats> getStatsByProjectAndTracker(@Param("projectId") Integer projectId, @Param("tracker") String tracker);

    /**
     * 根据时间范围获取统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE last_updated >= #{startTime} AND last_updated <= #{endTime}")
    List<IssueCategoryStats> getStatsByTimeRange(@Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 根据查询条件获取统计数据
     */
    @Select("<script>" +
            "SELECT " +
            "s.id, s.category_id, s.tracker_id, s.priority_id, s.project_id, s.sample_count, s.avg_processing_time_hours, " +
            "s.min_processing_time_hours, s.max_processing_time_hours, s.success_rate, s.retry_rate, s.last_updated, " +
            "c.name AS category_name, t.name AS tracker_name, p.name AS priority_name, pr.name AS project_name " +
            "FROM t_issue_category_stats s " +
            "LEFT JOIN t_issue_categorie c ON s.category_id = c.id " +
            "LEFT JOIN t_tracker t ON s.tracker_id = t.id " +
            "LEFT JOIN t_issue_priorities p ON s.priority_id = p.id " +
            "LEFT JOIN t_project pr ON s.project_id = pr.id " +
            "<where>" +
            "<if test='categoryId != null'>AND s.category_id = #{categoryId} </if>" +
            "<if test='trackerId != null'>AND s.tracker_id = #{trackerId} </if>" +
            "<if test='priorityId != null'>AND s.priority_id = #{priorityId} </if>" +
            "<if test='projectId != null'>AND s.project_id = #{projectId} </if>" +
            "<if test='sampleCount != null'>AND s.sample_count = #{sampleCount} </if>" +
            "<if test='avgProcessingTimeHours != null'>AND s.avg_processing_time_hours = #{avgProcessingTimeHours} </if>" +
            "<if test='minProcessingTimeHours != null'>AND s.min_processing_time_hours = #{minProcessingTimeHours} </if>" +
            "<if test='maxProcessingTimeHours != null'>AND s.max_processing_time_hours = #{maxProcessingTimeHours} </if>" +
            "<if test='successRate != null'>AND s.success_rate = #{successRate} </if>" +
            "<if test='retryRate != null'>AND s.retry_rate = #{retryRate} </if>" +
            "<if test='lastUpdated != null'>AND s.last_updated = #{lastUpdated} </if>" +
            "</where>" +
            "ORDER BY s.last_updated DESC" +
            "</script>")
    List<IssueCategoryStats> selectIssueCategoryStatsList(IssueCategoryStats queryParams);

    /**
     * 获取各类别的平均处理时间
     */
    @Select("SELECT tracker, AVG(avg_processing_hours) as avg_hours FROM t_issue_category_stats " +
            "WHERE project_id = #{projectId} GROUP BY tracker ORDER BY avg_hours ASC")
    List<Map<String, Object>> getAvgProcessingHoursByTracker(@Param("projectId") Integer projectId);

    /**
     * 获取各类别的处理数量
     */
    @Select("SELECT tracker, SUM(total_count) as total_count FROM t_issue_category_stats " +
            "WHERE project_id = #{projectId} GROUP BY tracker ORDER BY total_count DESC")
    List<Map<String, Object>> getTotalCountByTracker(@Param("projectId") Integer projectId);

    /**
     * 获取各类别的成功率
     */
    @Select("SELECT tracker, AVG(success_rate) as avg_success_rate FROM t_issue_category_stats " +
            "WHERE project_id = #{projectId} GROUP BY tracker ORDER BY avg_success_rate DESC")
    List<Map<String, Object>> getSuccessRateByTracker(@Param("projectId") Integer projectId);

    /**
     * 获取各类别的返工率
     */
    @Select("SELECT tracker, AVG(rework_rate) as avg_rework_rate FROM t_issue_category_stats " +
            "WHERE project_id = #{projectId} GROUP BY tracker ORDER BY avg_rework_rate ASC")
    List<Map<String, Object>> getReworkRateByTracker(@Param("projectId") Integer projectId);

    /**
     * 获取各项目不同类别的统计数据
     */
    @Select("SELECT project_id, project_name, tracker, avg_processing_hours, total_count, success_rate, rework_rate " +
            "FROM t_issue_category_stats ORDER BY project_id, tracker")
    List<Map<String, Object>> getAllProjectTrackerStats();

    /**
     * 根据优先级获取类别统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE priority = #{priority} ORDER BY last_updated DESC")
    List<IssueCategoryStats> getStatsByPriority(@Param("priority") Integer priority);

    /**
     * 根据项目ID和优先级获取统计数据
     */
    @Select("SELECT * FROM t_issue_category_stats WHERE project_id = #{projectId} AND priority = #{priority} ORDER BY last_updated DESC")
    List<IssueCategoryStats> getStatsByProjectAndPriority(@Param("projectId") Integer projectId, @Param("priority") Integer priority);

    /**
     * 根据类别ID删除统计记录
     */
    @Delete("DELETE FROM t_issue_category_stats WHERE category_id = #{categoryId}")
    int deleteByCategoryId(@Param("categoryId") Long categoryId);

    /**
     * 根据组合条件删除统计记录
     */
    @Delete("DELETE FROM t_issue_category_stats WHERE category_id = #{categoryId} " +
            "AND tracker_id = #{trackerId} AND priority_id = #{priorityId} AND project_id = #{projectId}")
    int deleteByCombination(@Param("categoryId") Long categoryId, @Param("trackerId") Long trackerId, 
                          @Param("priorityId") Long priorityId, @Param("projectId") Long projectId);

    /**
     * 获取所有不同的类别ID
     */
    @Select("SELECT DISTINCT category_id FROM t_issue_category_stats WHERE category_id IS NOT NULL")
    List<IssueCategoryStats> selectDistinctCategoryIds();
}